1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using System.IO;
11 using System.Security.Cryptography;
12 namespace WarehouseManagementSystem
13 {
14 public partial class frmProduct : Form
15 {
16 SqlDataReader rdr = null;
17 DataTable dtable = new DataTable();
18 SqlConnection con = null;
19 SqlCommand cmd = null;
20 DataTable dt = new DataTable();
21 ConnectionString cs = new ConnectionString();
22 public frmProduct()
23 {
24 InitializeComponent();
25 }
26 private void auto()
27 {
28 txtProductID.Text = "P-" + GetUniqueKey(6);
29 }
30 public static string GetUniqueKey(int maxSize)
31 {
32 char[] chars = new char[62];
33 chars = "123456789".ToCharArray();
34 byte[] data = new byte[1];
35 RNGCryptoServiceProvider crypto = new RNGCryptoServiceProvider();
36 crypto.GetNonZeroBytes(data);
37 data = new byte[maxSize];
38 crypto.GetNonZeroBytes(data);
39 StringBuilder result = new StringBuilder(maxSize);
40 foreach (byte b in data)
41 {
42 result.Append(chars[b % (chars.Length)]);
43 }
44 return result.ToString();
45 }
46 private void frmProduct_Load(object sender, EventArgs e)
47 {
48 FillCombo();
49 Autocomplete();
50 }
51 public void FillCombo()
52 {
53 try
54 {
55
56 con = new SqlConnection(cs.DBConn);
57 con.Open();
58 string ct = "select RTRIM(CategoryName) from Category order by CategoryName";
59 cmd = new SqlCommand(ct);
60 cmd.Connection = con;
61 rdr = cmd.ExecuteReader();
62
63 while (rdr.Read())
64 {
65 cmbCategory.Items.Add(rdr[0]);
66 }
67 con.Close();
68
69 }
70 catch (Exception ex)
71 {
72 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
73 }
74 }
75
76 private void Reset()
77 {
78 txtProductName.Text = "";
79 cmbSubCategory.Text = "";
80 cmbCategory.Text = "";
81 txtPrice.Text = "";
82 txtFeatures.Text = "";
83 pictureBox1.Image = Properties.Resources._12;
84 cmbSubCategory.Enabled = false;
85 btnDelete.Enabled = false;
86 btnUpdate.Enabled = false;
87 btnSave.Enabled = true;
88 txtProductName.Focus();
89 }
90
91 private void btnNew_Click(object sender, EventArgs e)
92 {
93 Reset();
94 }
95
96 private void btnSave_Click(object sender, EventArgs e)
97 {
98 if (txtProductName.Text == "")
99 {
100 MessageBox.Show("Please enter product name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
101 txtProductName.Focus();
102 return;
103 }
104 if (cmbCategory.Text == "")
105 {
106 MessageBox.Show("Please select category", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
107 cmbCategory.Focus();
108 return;
109 }
110 if (cmbSubCategory.Text == "")
111 {
112 MessageBox.Show("Please select sub category", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
113 cmbSubCategory.Focus();
114 return;
115 }
116 if (txtPrice.Text == "")
117 {
118 MessageBox.Show("Please enter price", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
119 txtPrice.Focus();
120 return;
121 }
122 try
123 {
124 con = new SqlConnection(cs.DBConn);
125 con.Open();
126 string ct = "select ProductName from Product where ProductName='" + txtProductName.Text + "'";
127
128 cmd = new SqlCommand(ct);
129 cmd.Connection = con;
130 rdr = cmd.ExecuteReader();
131
132 if (rdr.Read())
133 {
134 MessageBox.Show("Product Name Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
135 txtProductName.Text = "";
136 txtProductName.Focus();
137
138
139 if ((rdr != null))
140 {
141 rdr.Close();
142 }
143 return;
144 }
145 auto();
146 con = new SqlConnection(cs.DBConn);
147 con.Open();
148 string cb = "insert into Product(ProductID,ProductName,CategoryID,SubCategoryID,Features,Price,Image) VALUES ('" + txtProductID.Text + "','" + txtProductName.Text + "'," + txtCategoryID.Text+ "," + txtSubCategoryID.Text+ ",@d1,"+ txtPrice.Text +",@d2)";
149 cmd = new SqlCommand(cb);
150 cmd.Connection = con;
151 cmd.Parameters.AddWithValue("@d1", txtFeatures.Text);
152 MemoryStream ms = new MemoryStream();
153 Bitmap bmpImage = new Bitmap(pictureBox1.Image);
154 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
155 byte[] data = ms.GetBuffer();
156 SqlParameter p = new SqlParameter("@d2", SqlDbType.Image);
157 p.Value = data;
158 cmd.Parameters.Add(p);
159 cmd.ExecuteReader();
160 con.Close();
161 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
162 Autocomplete();
163 btnSave.Enabled = false;
164 }
165 catch (Exception ex)
166 {
167 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
168 }
169 }
170
171 private void btnDelete_Click(object sender, EventArgs e)
172 {
173 if (MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
174 {
175 delete_records();
176 }
177 }
178 private void delete_records()
179 {
180
181 try
182 {
183
184 int RowsAffected = 0;
185 con = new SqlConnection(cs.DBConn);
186 con.Open();
187 string cq = "delete from product where productID='" + txtProductID.Text + "'";
188 cmd = new SqlCommand(cq);
189 cmd.Connection = con;
190 RowsAffected = cmd.ExecuteNonQuery();
191 if (RowsAffected > 0)
192 {
193 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
194 Reset();
195 Autocomplete();
196 }
197 else
198 {
199 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information);
200 Reset();
201 Autocomplete();
202 }
203 if (con.State == ConnectionState.Open)
204 {
205 con.Close();
206 }
207
208
209 }
210 catch (Exception ex)
211 {
212 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
213 }
214 }
215 private void Autocomplete()
216 {
217 try
218 {
219 con = new SqlConnection(cs.DBConn);
220 con.Open();
221 SqlCommand cmd = new SqlCommand("SELECT distinct ProductName FROM product", con);
222 DataSet ds = new DataSet();
223 SqlDataAdapter da = new SqlDataAdapter(cmd);
224 da.Fill(ds, "Product");
225 AutoCompleteStringCollection col = new AutoCompleteStringCollection();
226 int i = 0;
227 for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
228 {
229 col.Add(ds.Tables[0].Rows[i]["productname"].ToString());
230
231 }
232 txtProductName.AutoCompleteSource = AutoCompleteSource.CustomSource;
233 txtProductName.AutoCompleteCustomSource = col;
234 txtProductName.AutoCompleteMode = AutoCompleteMode.Suggest;
235
236 con.Close();
237 }
238 catch (Exception ex)
239 {
240 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
241 }
242 }
243
244 private void btnUpdate_Click(object sender, EventArgs e)
245 {
246 if (txtProductName.Text == "")
247 {
248 MessageBox.Show("Please enter product name", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
249 txtProductName.Focus();
250 return;
251 }
252 if (cmbCategory.Text == "")
253 {
254 MessageBox.Show("Please select category", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
255 cmbCategory.Focus();
256 return;
257 }
258 if (cmbSubCategory.Text == "")
259 {
260 MessageBox.Show("Please select sub category", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
261 cmbSubCategory.Focus();
262 return;
263 }
264 if (txtPrice.Text == "")
265 {
266 MessageBox.Show("Please enter price", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
267 txtPrice.Focus();
268 return;
269 }
270 try
271 {
272
273 con = new SqlConnection(cs.DBConn);
274 con.Open();
275 string cb = "Update product set ProductName='" + txtProductName.Text + "',CategoryID=" + txtCategoryID.Text + ",SubCategoryID=" + txtSubCategoryID.Text + ",Features=@d1,price=" + txtPrice.Text + ",Image=@d2 Where ProductID='" + txtProductID.Text + "'";
276 cmd = new SqlCommand(cb);
277 cmd.Connection = con;
278 cmd.Parameters.AddWithValue("@d1", txtFeatures.Text);
279 MemoryStream ms = new MemoryStream();
280 Bitmap bmpImage = new Bitmap(pictureBox1.Image);
281 bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
282 byte[] data = ms.GetBuffer();
283 SqlParameter p = new SqlParameter("@d2", SqlDbType.Image);
284 p.Value = data;
285 cmd.Parameters.Add(p);
286 cmd.ExecuteReader();
287 con.Close();
288 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information);
289 Autocomplete();
290 btnUpdate.Enabled = false;
291 }
292 catch (Exception ex)
293 {
294 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
295 }
296 }
297
298 private void btnGetData_Click(object sender, EventArgs e)
299 {
300 this.Hide();
301 frmProductsRecord2 frm = new frmProductsRecord2();
302 frm.Show();
303 frm.GetData();
304 }
305
306 private void button1_Click(object sender, EventArgs e)
307 {
308 try
309 {
310 var _with1 = openFileDialog1;
311
312 _with1.Filter = ("Image Files |*.png; *.bmp; *.jpg;*.jpeg; *.gif;");
313 _with1.FilterIndex = 4;
314 //Reset the file name
315 openFileDialog1.FileName = "";
316
317 if (openFileDialog1.ShowDialog() == DialogResult.OK)
318 {
319 pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);
320 }
321
322 }
323 catch (Exception ex)
324 {
325 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
326 }
327 }
328
329 private void txtPrice_KeyPress(object sender, KeyPressEventArgs e)
330 {
331 // allows 0-9, backspace, and decimal
332 if (((e.KeyChar < 48 || e.KeyChar > 57) && e.KeyChar != 8 && e.KeyChar != 46))
333 {
334 e.Handled = true;
335 return;
336 }
337 }
338
339 private void cmbCategory_SelectedIndexChanged(object sender, EventArgs e)
340 {
341 try
342 {
343 con = new SqlConnection(cs.DBConn);
344
345 con.Open();
346 cmd = con.CreateCommand();
347
348 cmd.CommandText = "SELECT ID from Category WHERE CategoryName = '" + cmbCategory.Text + "'";
349 rdr = cmd.ExecuteReader();
350
351 if (rdr.Read())
352 {
353 txtCategoryID.Text = rdr.GetInt32(0).ToString().Trim();
354 }
355 if ((rdr != null))
356 {
357 rdr.Close();
358 }
359 if (con.State == ConnectionState.Open)
360 {
361 con.Close();
362 }
363 cmbCategory.Text = cmbCategory.Text.Trim();
364 cmbSubCategory.Items.Clear();
365 cmbSubCategory.Text = "";
366 cmbSubCategory.Enabled = true;
367 cmbSubCategory.Focus();
368
369 con = new SqlConnection(cs.DBConn);
370 con.Open();
371 string ct = "select distinct RTRIM(SubCategoryName) from Category,SubCategory where Category.ID=SubCategory.CategoryID and CategoryName= '" + cmbCategory.Text + "'";
372 cmd = new SqlCommand(ct);
373 cmd.Connection = con;
374 rdr = cmd.ExecuteReader();
375
376 while (rdr.Read())
377 {
378 cmbSubCategory.Items.Add(rdr[0]);
379 }
380 con.Close();
381
382 }
383
384 catch (Exception ex)
385 {
386 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
387 }
388
389
390 }
391
392 private void cmbSubCategory_SelectedIndexChanged(object sender, EventArgs e)
393 {
394 try
395 {
396 con = new SqlConnection(cs.DBConn);
397
398 con.Open();
399 cmd = con.CreateCommand();
400
401 cmd.CommandText = "SELECT ID from SubCategory WHERE SubCategoryName = '" + cmbSubCategory.Text + "'";
402 rdr = cmd.ExecuteReader();
403
404 if (rdr.Read())
405 {
406 txtSubCategoryID.Text = rdr.GetInt32(0).ToString().Trim();
407 }
408 if ((rdr != null))
409 {
410 rdr.Close();
411 }
412 if (con.State == ConnectionState.Open)
413 {
414 con.Close();
415 }
416 }
417
418 catch (Exception ex)
419 {
420 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
421 }
422
423 }
424 }
425 }
